set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;
--出仓表
with tab_barscan_deliver_base as (
    select
     waybill_no,
    scan_site_code,
       scan_site,
       scan_time,
           scan_user,
           scan_user_code
    from (
            select
       waybill_no,
    scan_site_code,
       scan_site,
       scan_time,
           scan_user,
           scan_user_code,
           row_number() over (partition by waybill_no order by scan_time) as rn
from jms_dwd.dwd_tab_barscan_deliver_base_hi

where dt>=date_format(date_sub(substring('{{ execution_date | cst_hour }}',0,10),8),'yyyy-MM-dd-00')
and dt<=date_format(date_add(substring('{{ execution_date | cst_hour }}',0,10),1),'yyyy-MM-dd-00')
        and to_date(scan_time)>=date_format(date_sub(substring('{{ execution_date | cst_hour }}',0,10),6),'yyyy-MM-dd')
        and to_date(scan_time)<=date_format(substring('{{ execution_date | cst_hour }}',0,10),'yyyy-MM-dd')
             )tmp
    where tmp.rn=1
),
-- 签收表
 sign_detail  as (--时效签收
     select waybill_no,
            min(wid_columns)[0] as sign_time,
            min(wid_columns)[1] as sign_network_code,
            min(wid_columns)[2] as sign_scan_type,
            min(wid_columns)[3] as sign_user_code,
            min(wid_columns)[4] as waybill_source
     from (
              select waybill_no,
                     split(concat(scantime, '\001',
                                  coalesce(scansitecode, ''), '\001',
                                  coalesce(scantype, ''), '\001',
                                  coalesce(send_deliver_usercode, ''), '\001',
                                  coalesce(source, ''), '\001'
                               ), '\001') as wid_columns
              from (
                       select waybill_no  ,
                              signed_time as   scantime,
                              signed_site_code scansitecode,
                              '快件签收'      as   scantype,
                              send_user_code   send_deliver_usercode,
                              '快件签收' as source
                       from jms_dwd.dwd_tab_barscan_sign_base_hi
                       where dt between date_format(date_add(substring('{{ execution_date | cst_hour }}',0,10), -6), 'yyyy-MM-dd-00')
                           and date_format(date_add(substring('{{ execution_date | cst_hour }}',0,10), 1), 'yyyy-MM-dd-00')
                         and length(waybill_no) = 15
                       union all
                       select waybill_no,
                              scan_time      scantime,
                              scan_site_code scansitecode,
                              scan_type         scantype,
                              send_deliver_usercode,
                              source
                       from jms_dwd.dwd_tab_barscan_collect_base_hi
                       where dt between date_format(date_add(substring('{{ execution_date | cst_hour }}',0,10), -6), 'yyyy-MM-dd-00')
                           and date_format(date_add(substring('{{ execution_date | cst_hour }}',0,10), 1), 'yyyy-MM-dd-00')
                         and length(waybill_no) = 15
                         and scan_type in ('代理点收入扫描', '出库扫描', '入库扫描')
                   ) t
          ) t
     group by waybill_no
 ),

-- 问题件
 t_piece as (--问题件类型表,获取问题件一级类型
    select id,
           code,
           name,
           parent_id--父级类型id
    from jms_dim.dim_lmdm_sys_abnormal_piece
    where  is_delete = 1
    and is_enable = 1
),
barscan_difficult_base as (
    select waybill_no,
           date_format(scan_time,'yyyy-MM-dd HH:mm:ss') as difficult_time,
           problem_type as difficult_type,
           difficult_first_type
    from (
          select waybill_no ,
                 scan_time,
                 problem_type,--问题件类型
                 t_piece2.name as difficult_first_type,--问题件一级类型
                 row_number() over (partition by waybill_no order by scan_time desc) bill_rank
          from jms_dwd.dwd_tab_barscan_difficult_base_hi t
          left join t_piece t_piece1 on t.problem_type_code = t_piece1.code  --remark2是问题件二级类型
          left join t_piece t_piece2 on t_piece1.parent_id = t_piece2.id
          where dt between date_format(date_add(substring('{{ execution_date | cst_hour }}',0,10), -20), 'yyyy-MM-dd-00')
            and date_format(date_add(substring('{{ execution_date | cst_hour }}',0,10), 1), 'yyyy-MM-dd-00')
            and length(waybill_no) = 15
        ) t where bill_rank = 1
),
     network_detail as (
         select virt_code,
                virt_name,
                manage_code,
                manage_name,
                agent_code,
                agent_name,
                fran_code,
                fran_name,
                code,
                name,
                provider_desc,
                provider_id,
                city_id,
                city_desc,
                area_id,
                area_desc,
                network_type
         from jms_dim.dim_network_whole_massage
         where is_delete=1
         and is_enable=1
         and network_type in (6,5)
     )
 insert overwrite table jms_dm.dm_deliver_sign_hour_detail_dt partition (dt)
select
      t1.waybill_no
      ,t4.virt_code
     , t4.virt_name
     , t4.manage_code
     , t4.manage_name
     , t4.agent_code
     , t4.agent_name
     , t4.fran_code
     , t4.fran_name
     , t1.scan_site_code
     , t1.scan_site
     , t4.provider_desc
     , t4.provider_id
     , t4.city_id
     , t4.city_desc
     , t4.area_id
     , t4.area_desc
     ,t1. scan_user
     ,t1.scan_user_code
    , t1.scan_time
    ,t2.sign_time
    ,t2.sign_scan_type
    ,t3.difficult_first_type
    ,t3.difficult_type
   ,if(t3.difficult_first_type='退回件',1,0) as is_return
   , t3.difficult_time
  ,to_date(t1.scan_time) as date_time
    , t2.waybill_source   -- 签收信息
     ,case when sign_time is not null then datediff(sign_time,scan_time) end as sign_aging
    ,t4.network_type
  ,to_date(t1.scan_time) as dt
from tab_barscan_deliver_base t1
left join sign_detail  t2
on  t1.waybill_no=t2.waybill_no
left join barscan_difficult_base t3
on t1.waybill_no=t3.waybill_no
 join network_detail t4
on t1.scan_site_code=t4.code
where t1.scan_time<=nvl(t2.sign_time,'2049-12-31 23:59:59')
distribute by dt,pmod(hash(rand()),60);